﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;

/// <summary>
///连接数据库，获取相应信息
/// </summary>
public class GetData
{
    public GetData()
    {
        ddc = new databaseDataContext();
    }

    databaseDataContext ddc = null;

    public IEnumerable<dynamic> getname()
    {
        var query=from o in ddc.members
                  where(o.uid%2==0)
                  select new{
                      uid=o.uid,
                      username=o.username
                  };
        return query;
    }


    public List<forums> GetAllForum()//获取所有版块
    { 
        var query =from o in ddc.forums
                   select o;
        if (query.Count() == 0)
            return null;
        else
            return query.ToList();
    }

    public List<forums> GetAllVisibleForum()//获取所有版块
    {
        var query = from o in ddc.forums
                    where(o.visible==1)
                    select o;
        if (query.Count() == 0)
            return null;
        else
            return query.ToList();
    }

    public forums GetForum(int fid)//获取指定板块
    {
        var query=from o in ddc.forums
                  where(o.fid==fid)
                  select o;
        if (query.Count() == 0)
            return null;
        else
            return (query.ToList())[0];
    }

    public List<posts> GetPosts()//获取所有回帖
    {
        var query = from o in ddc.posts
                    select o;
        List<posts> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public int GetPostCount(int tid)//获取回帖数量 
    {
        int count = (from o in ddc.posts
                     where (o.tid == tid)
                     select o).Count();
        return count;
    }

    public List<posts> GetPosts(int tid)//获取指定tid的回帖
    {
        var query = from o in ddc.posts
                    where (o.tid == tid)
                    select o;
        List<posts> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public posts GetOnePosts(int pid)//获取指定tid的回帖
    {
        var query = from o in ddc.posts
                    where (o.pid == pid)
                    select o;
        posts res = null;
        if (query.Count() != 0)
            res = (query.ToList())[0];
        return res;
    }

    public List<posts> GetPosts(int tid, int pageSize, int pageIndex)//获取指定tid的回帖,并翻页
    {
        int startIndex = 0;
        if (pageIndex == 1)
            startIndex = 0;
        else
            startIndex = (pageIndex - 1) * pageSize - 1;
        var query = (from o in ddc.posts
                     where (o.tid == tid)
                     orderby o.pid
                     select o).Skip(startIndex).Take(pageSize);
        if (query.Count() == 0)
            return null;
        else
            return query.ToList();

    }

    public List<posts> GetPosts(int tid, int uid)//获取指定tid和uid的回帖,如果tid==-1,则获取该用户所有回帖
    {
        if (tid == -1)
        {
            var query = from o in ddc.posts
                        where (o.uid == uid)
                        select o;
            List<posts> res = null;
            if (query.Count() != 0)
                res = query.ToList();
            return res;
        }
        else
        {
            var query = from o in ddc.posts
                        where (o.tid == tid && o.uid == uid)
                        select o;
            List<posts> res = null;
            if (query.Count() != 0)
                res = query.ToList();
            return res;
        }
    }

    public List<threads> GetThreads()// 获取所有主题
    {
        var query = from o in ddc.threads
                    select o;
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public int GetThreadsCount(int fid)// 获取指定板块主题数目,只获取未删除
    {
        int count = 0;
        count = (from o in ddc.threads
                    where (o.fid == fid&&o.status==1)
                    select o).Count();
        return count;
    }

    public int GetThreadsAllCount(int fid)// 获取指定板块主题数目，获取全部
    {
        int count = 0;
        count = (from o in ddc.threads
                 where (o.fid == fid)
                 select o).Count();
        return count;
    }

    public int GetTodayThreadCount(int fid,DateTime d)//获取指定fid当日发帖量
    {
        int count=0;
        count = (from o in ddc.threads
                 where (o.fid == fid && o.posttime.Date == d.Date)
                 select o).Count();
        return count;
    }

    public List<threads> GetThreads(int fid)// 获取指定板块主题帖子
    {
        var query = from o in ddc.threads
                    where (o.fid == fid)
                    select o;
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetThreads(int fid,int pageIndex,int pageSize)// 获取翻页指定板块主题,分页,只获取未被删除的
    {
        int startIndex = 0;
        if (pageIndex == 1)
            startIndex = 0;
        else
            startIndex = (pageIndex - 1) * pageSize;
        var query = (from o in ddc.threads
                    where (o.fid == fid&&o.status==1)
                    orderby o.lastreplytime descending
                     select o).Skip(startIndex).Take(pageSize);
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetAllThreads(int fid, int pageIndex, int pageSize)// 获取翻页指定板块主题,分页，获取全部
    {
        int startIndex = 0;
        if (pageIndex == 1)
            startIndex = 0;
        else
            startIndex = (pageIndex - 1) * pageSize;
        var query = (from o in ddc.threads
                     where (o.fid == fid)
                     orderby o.lastreplytime descending
                     select o).Skip(startIndex).Take(pageSize);
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetSearchAojProb(int fid, int aid, int pageIndex, int pageSize)
    {
        int startIndex = 0;
        if (pageIndex == 1)
            startIndex = 0;
        else
            startIndex = (pageIndex - 1) * pageSize -1;
        var query = (from o in ddc.threads
                     where (o.fid == fid&&o.status==1&&o.ProbID==aid)
                     orderby o.lastreplytime descending
                     select o).Skip(startIndex).Take(pageSize);
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetSearchAllAojProb(int fid, int aid, int pageIndex, int pageSize)
    {
        int startIndex = 0;
        if (pageIndex == 1)
            startIndex = 0;
        else
            startIndex = (pageIndex - 1) * pageSize - 1;
        var query = (from o in ddc.threads
                     where (o.fid == fid  && o.ProbID == aid)
                     orderby o.lastreplytime descending
                     select o).Skip(startIndex).Take(pageSize);
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetThreads(string title)// 获取指定标题主题
    {
        var query = from o in ddc.threads
                    where (o.title.Contains(title))
                    select o;
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public threads GetOneThread(int tid)// 获取指定tid主题
    {
        var query = from o in ddc.threads
                    where (o.tid==tid)
                    select o;
        threads res = null;
        if (query.Count() != 0)
            res = (query.ToList())[0];
        return res;
    }

    public List<threads> GetThreads(string title, string text)// 获取指定标题、内容主题
    {
        var query = from o in ddc.threads
                    where (o.title.Contains(title) || (o.text.Contains(text)))
                    select o;
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetUserThreads(int uid)// 获取指定作者主题
    {
        var query = from o in ddc.threads
                    where (o.uid == uid)
                    select o;
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetLatestThreads(int top)// 获取最近发表主题
    {
        var query = (from o in ddc.threads
                     orderby o.posttime descending
                     select o).Take(top);
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<threads> GetLastPostThreads(int top)// 获取最近回复主题
    {
        var query = (from o in ddc.threads
                     where(o.status==1)
                     orderby o.lastreplytime descending
                     select o).Take(top);
        List<threads> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public List<members> GetMembers()// 获取所有用户
    {
        var query = from o in ddc.members
                    select o;
        List<members> res = null;
        if (query.Count() != 0)
            res = query.ToList();
        return res;
    }

    public members GetOneMember(string name)// 获取指定用户名用户
    {
        members m = null;
        var query = from o in ddc.members
                    where (string.Equals(o.username, name))
                    select o;
        if (query.Count() != 0)
            m = (query.ToList())[0];
        return m;
    }

    public List<members> SearchMemberUsername(string name)
    {
        List<members> mem = new List<members>();
        var query = from o in ddc.members
                    where (o.username.Contains(name))
                    select o;
        if (query.Count() != 0)
            mem = query.ToList();
        return mem;
    }

    public members GetOneMember(int uid)//获取指定uid的用户
    {
        members m = null;
        var query = from o in ddc.members
                    where (o.uid == uid)
                    select o;
        if (query.Count() != 0)
            m = (query.ToList())[0];
        return m;
    }

    public members GetOneMemberInUname(string name)//获取指定nickname用户
    {
        members m = null;
        var query = from o in ddc.members
                    where (string.Equals(o.nickname, name))
                    select o;
        if (query.Count() != 0)
            m = (query.ToList())[0];
        return m;
    }

    public List<members> SearchMemberNickname(string name)
    {
        List<members> mem = new List<members>();
        var query = from o in ddc.members
                    where (o.nickname.Contains(name))
                    select o;
        if (query.Count() != 0)
            mem = query.ToList();
        return mem;
    }


    public int GetHasAvatar(int uid)//获取指定用户是否有头像
    {
        var query = from o in ddc.members
                    where (o.uid == uid)
                    select o.hasavatar;
        int ans = (query.ToList())[0];
        return ans;
    }

    public DateTime GetLastPostTime(int uid)
    {
        var query = from o in ddc.members
                    where (o.uid == uid)
                    select o;
        return (DateTime)((query.ToList())[0].lastpost_time);
    }

    public List<groups> GetAllGroup()//获取所有用户组信息
    {
        var query = from o in ddc.groups
                    select o;
        if (query.Count() != 0)
            return query.ToList();
        else
            return null;
    }

    public groups GetOneGroup(int gid)//获取指定gid用户组信息
    {
        var query = from o in ddc.groups
                    where (o.gid == gid)
                    select o;
        if (query.Count() != 0)
            return (query.ToList())[0];
        else
            return null;

    }

    public List<notices> GetUnReadPostNotice(int uid,int pageIndex,int pageSize)//获取未帖子读消息
    {
        int startIndex = (pageIndex - 1) * pageSize;
        var query = (from o in ddc.notices
                     where (o.toUid == uid && o.isReaded==0)
                     orderby o.sendtime descending
                     select o).Skip(startIndex).Take(pageSize);
        List<notices> no = null;
        if (query.Count() != 0)
            no= query.ToList();
        return no;
    }

    public List<notices> GetUnReadSystemNotice(int uid, int pageIndex, int pageSize)//获取未帖子读消息
    {
        int startIndex = (pageIndex - 1) * pageSize;
        var query = (from o in ddc.notices
                     where (o.toUid == uid && o.isReaded == 0 && o.fromUid==1)
                     orderby o.sendtime descending
                     select o).Skip(startIndex).Take(pageSize);
        List<notices> no = null;
        if (query.Count() != 0)
            no = query.ToList();
        return no;
    }

    public int GetUnReadPostNoticeCount(int uid)//获取未读消息个数
    {
        var query = from o in ddc.notices
                    where (o.toUid == uid && o.isReaded == 0)
                    select o;
        return query.Count();
    }

    public List<notices> GetAllPostNotice(int uid, int pageIndex, int pageSize)//获取全部消息
    {
        int startIndex = 0;
        if (pageIndex == 1)
            startIndex = 0;
        else
            startIndex = (pageIndex - 1) * pageSize;
        var query = (from o in ddc.notices
                     where (o.toUid == uid )
                     orderby o.sendtime descending
                     select o).Skip(startIndex).Take(pageSize);
        if (query.Count() != 0)
            return query.ToList();
        else
            return null;
    }

    public notices GetOnePostNotice(int nid)//获取一个消息
    {
        var query = from o in ddc.notices
                    where (o.nid == nid)
                    select o;
        if (query.Count() != 0)
            return (query.ToList())[0];
        else
            return null;
    }
}